package com.lill.common.component.util;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelUtil {
	
	protected static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 填充数据Excel表头、数据
     * @param sheet
     * @param rowNum
     * @param vals
     */
    public static void createExcel(XSSFSheet sheet, int rowNum, String[] vals){
        XSSFRow row = sheet.createRow(rowNum);
        for(int i=0;i<vals.length;i++){
            row.createCell(i).setCellValue(vals[i]);
        }
    }
    /**
     * 重载方法，加入样式
     * 填充数据Excel表头、数据
     * @param sheet
     * @param rowNum
     * @param vals
     */
    public static void createExcel(XSSFSheet sheet, int rowNum, String[] vals, CellStyle cellStyle){
        XSSFRow row = sheet.createRow(rowNum);
        row.setRowStyle(cellStyle);
        for(int i=0;i<vals.length;i++){
            row.createCell(i).setCellValue(vals[i]);
        }
    }

    /**
     * 导出配置
     * @param excelName
     * @param response
     * @return
     * @throws IOException
     */
    public static ServletOutputStream generateResponseExcel(String excelName, HttpServletResponse response) throws IOException {
        excelName = excelName == null || "".equals(excelName) ? "excel" : URLEncoder.encode(excelName, "UTF-8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + excelName + ".xlsx");
        return response.getOutputStream();
    }

    /**
     * 生成文件名称
     * @param name
     * @return
     */
    public static String excelName(String name){
        return  name + DateUtils.format(new Date(),DateUtils.DATE_TIME_PATTERN_YYYY_MM_DD_HH_MM_SS_SSS);
    }

    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel


    /**
     * 获取实体对象返回属性名称
     * @param obj 实体对象
     * @return
     * @throws Exception
     */
    public static java.lang.reflect.Field[] findEntityAllTypeName(Object obj)throws Exception{

        Class<? extends Object> cls = obj.getClass();

        return cls.getDeclaredFields();
    }

    /**
     * 根据文件选择excel版本
     * @return
     * @throws Exception
     */
    public static Workbook chooseWorkbook(MultipartFile file) throws Exception{
        Workbook workbook = null;
        if(null==file){
        	workbook = new XSSFWorkbook(new FileInputStream("E:/tmp/1.1.xlsx"));
        }else{
        	String filename = file.getOriginalFilename();
        	String fileType = (filename.substring(filename.lastIndexOf("."), filename.length())).toLowerCase();
        	if(excel2003L.equals(fileType)){
        		workbook = new HSSFWorkbook(file.getInputStream());  //2003-
        	}else if(excel2007U.equals(fileType)){
        		workbook = new XSSFWorkbook(file.getInputStream());  //2007+
        	}else{
        		throw new Exception("解析的文件格式有误！");
        	}
        }


        return workbook;
    }


    /**
     * 公共的导入excel方法
     * @param file 文件
     * @param sheetname 工作簿名称
     * @param obj 实体类
     * @return
     * @throws IOException
     */
    public static List<Object> importBaseExcel(MultipartFile file, String sheetname, Object obj) throws IOException{

        Workbook workbook = null;

        try {
            //读取文件内容
            workbook = chooseWorkbook(file);
            workbook.getSheetName(0);

            //获取工作表
            Sheet sheet = workbook.getSheet(sheetname);
            if (sheet==null){
                sheet=workbook.getSheetAt(0);
            }

            //获取sheet中第一行行号
            int firstRowNum = sheet.getFirstRowNum();
            //获取sheet中最后一行行号
            int lastRowNum = sheet.getLastRowNum();

            //获取该实体所有定义的属性 返回Field数组
            java.lang.reflect.Field[] entityName = findEntityAllTypeName(obj);

            String classname =  obj.getClass().getName();
            Class<?> clazz = Class.forName(classname);

            List<Object> list = new ArrayList<Object>();

            //循环插入数据
            for(int i=firstRowNum+1;i<=lastRowNum;i++){

                Row row = sheet.getRow(i);
                if(null==row){
                	continue;
                }
                //可以根据该类名生成Java对象
                Object pojo =  clazz.newInstance();
                boolean isEmpty = false;
                //除自增编号外，实体字段匹配sheet列
                for(int j = 0;j < row.getLastCellNum();j++){

                    //获取属性的名字,将属性的首字符大写，方便构造set方法
                	if((j+1)>=entityName.length){
                		continue;
                	}
                    String name = "set"+entityName[j+1].getName().substring(0, 1).toUpperCase().concat(entityName[j+1].getName().substring(1))+"";
                    //获取属性的类型
                    String type = entityName[j+1].getGenericType().toString();

                    Method m = null;
                    //getMethod只能调用public声明的方法，而getDeclaredMethod基本可以调用任何类型声明的方法
                    m = obj.getClass().getDeclaredMethod(name,entityName[j+1].getType());
                    Cell pname = row.getCell(j);
                    if(pname != null && !isEmpty){
                        String value = pname.toString().trim();
                        if(value.length() > 0){
                            isEmpty = true;
                        }else{
                            continue;
                        }
                    }

                    //根据属性类型装入值
                 if(null!=pname && !"".equals(getVal(pname).trim())){
                    switch (type) {
                        case "char":
                        case "class java.lang.Character":
                        case "class java.lang.String":
                            m.invoke(pojo,getVal(pname).trim());
                            break;
                        case "int":
                        case "class java.lang.Integer":
                            m.invoke(pojo, new Double(getVal(pname).trim()).intValue());
                            break;
                        case "class java.util.Date":
                            m.invoke(pojo,getVal(pname));
                            break;
                        case "float":
                        case "double":
                        case "class java.lang.Double":
                            m.invoke(pojo,new Double(getVal(pname).trim()));
                            break;
                        case "class java.lang.Float":
                        case "class java.lang.Long":
                        case "class java.lang.Short":
                        case "class java.math.BigDecimal":
                            m.invoke(pojo,new BigDecimal((StringUtils.isEmpty(getVal(pname))?"0":getVal(pname).trim())));
                            break;
                        default:
                            break;
                    }
                    }
                 }
                if(isEmpty){
                    list.add(pojo);
                }
            }
            return list;
        } catch (Exception e) {
        	logger.error(e.getMessage(), e);
        } finally {
            workbook.close();
        }
        return null;
    }



    /**
     *
     */

    /**
     * 处理类型
     * @param cell
     * @return
     */
    @SuppressWarnings("deprecation")
	public static String getVal(Cell cell) {
        if (null != cell) {

            switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC: // 数字
                    if(0==cell.getCellType()){
                        if(HSSFDateUtil.isCellDateFormatted(cell)){
                            Date d = cell.getDateCellValue();
                            DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            return formater.format(d);
                        }
                    }
                    String val = cell.getNumericCellValue()+"";
                    String num = new BigDecimal(val).toPlainString();
//                    int index = val.indexOf(".");
//                    if(val.length()>10){
//                        DecimalFormat df = new DecimalFormat("0");//处理科学计数法
//                        return df.format(cell.getNumericCellValue());
//                    }else{
//                        if(Integer.valueOf(val.substring(index+1)) == 0){
//                            DecimalFormat df = new DecimalFormat("0");//处理科学计数法
//                            return df.format(cell.getNumericCellValue());
//                        }
//                    }
//                    return cell.getNumericCellValue()+"";//double
                    return num;
                case XSSFCell.CELL_TYPE_STRING: // 字符串
                    return cell.getStringCellValue() + "";
                case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    return cell.getBooleanCellValue() + "";
                case XSSFCell.CELL_TYPE_FORMULA: // 公式
                    try{
                        if(HSSFDateUtil.isCellDateFormatted(cell)){
                            Date date = cell.getDateCellValue();
                            return (date.getYear() + 1900) + "-" + (date.getMonth() + 1) +"-" + date.getDate();
                        }else{
                            return String.valueOf(cell.getNumericCellValue());
                        }
                    }catch (IllegalStateException e) {
                        return  String.valueOf(cell.getRichStringCellValue());
                    }
                case XSSFCell.CELL_TYPE_BLANK: // 空值
                    return "";
//                case HSSFCell.CELL_TYPE_FORMULA: return "";
                case XSSFCell.CELL_TYPE_ERROR: // 故障
                    return "";
                default:
                    return "未知类型   ";
            }
        } else {
            return "";
        }
    }
}
